package com.nit.tools;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class JDBCUtils {
    private static  Connection conn;

    private static  String url = "jdbc:mysql://127.0.0.1:3306/nit-tools?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true";
    private static  String driver = "com.mysql.cj.jdbc.Driver";
    private static  String username = "root";
    private static  String password = "pbcom135789";

    /**
     * 静态代码块，获取配置信息
     */
    static {

        try {
            // 注册驱动
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public JDBCUtils(String url, String driver, String username, String password) {
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询接口
     *
     * @param sql
     * @return
     */
    public List<Map> query(String sql) {
//        Connection conn = JDBCUtils.getConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            //存储返回结果
            List<Map> list = new ArrayList();
            //获取表结构
            ResultSetMetaData metaData = rs.getMetaData();
            //获取字段数
            int columnCount = metaData.getColumnCount();
            // 展开结果集数据库
            while (rs.next()) {
                Map map = new HashMap();
                for (int i = 1; i <= columnCount; i++) {
                    //获取字段名称 metaData.getColumnName(i)
                    map.put(metaData.getColumnName(i), rs.getObject(i));
                }
                System.out.println(map.toString());
                list.add(map);
            }

            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(rs, ps, conn);
        }
        return null;
    }

    /***
     * https://blog.csdn.net/java2edition/article/details/126852267
     * 表元数据
     * @param tableName
     */
    public void getMeta(String tableName) {
        try {
            DatabaseMetaData databaseMetaData = conn.getMetaData();

            ResultSet rs = databaseMetaData.getTables(null, null, tableName, null);
            if (rs.next()) {
                rs.getString("");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }


    }

//    public  void batchInsert(String tableName) {
//        long start = System.currentTimeMillis();
//
//
//        NitIdcardGenerator idCardGenerator = new NitIdcardGenerator();
//
//        String sql = "insert into a(name,phone,idcard,job) VALUES (?,?,?,?)";
//        PreparedStatement ps = null;
//        try {
//            ps = this.conn.prepareStatement(sql);
//            for (int i = 10; i <= 1000000; i++) {
//
//                String name = NitChineseNameGenerator.generate();
//                String phone = NitPhoneGenerator.generate();
//                String idCard = idCardGenerator.generate();
//                String job = NitJobGenerator.generate();
//                ps.setObject(1, name);
//                ps.setObject(2, phone);
//                ps.setObject(3, idCard);
//                ps.setObject(4, job);
//
//                ps.addBatch();
//                if (i % 500 == 0) {
//                    ps.executeBatch();
//                    ps.clearBatch();
//                }
//            }
//            int num[] = ps.executeBatch();
//            ps.clearBatch();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            this.close(null, ps, this.conn);
//        }
//        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start) + "【单位：毫秒】");
//    }


    public static void batchInsert(List<Map> list) {
        long start = System.currentTimeMillis();

        String sql = "insert into oss_image(classify_id,img_url,create_time,source_name,source_url) VALUES (?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (Map<String, String> map : list) {

                String img_url = (String) map.get("img_url");
                String source_name = (String) map.get("source_name");
                String source_url = (String) map.get("source_url");

                ps.setObject(1, 1);
                ps.setObject(2, img_url);
                ps.setObject(3, LocalDateTime.now());
                ps.setObject(4, source_name);
                ps.setObject(5, source_url);

                ps.addBatch();
            }
            ps.executeBatch();
            int num[] = ps.executeBatch();
            ps.clearBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(null, ps, conn);
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start) + "【单位：毫秒】");
    }


    public Connection getConnection() {
        return conn;
    }

    /**
     * 释放资源
     *
     * @param rs   结果集对象
     * @param stmt 执行SQL语句对象
     * @param conn 连接对象
     */
    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void close(Statement stmt, Connection conn) {
        close(null, stmt, conn);
    }
}